

USE PEDIDOS;

SET SHOWPLAN_ALL OFF;
SET SHOWPLAN_ALL ON;
GO

SELECT CODVEN, VLR_TOTAL, MONTH(DATA_EMISSAO) AS MES 
      FROM PEDIDOS
      WHERE YEAR(DATA_EMISSAO) = 2006;


SELECT CODVEN, [1] AS MES1, [2] AS MES2, [3] AS MES3, [4] AS MES4, [5] AS MES5, 
               [6] AS MES6, [7] AS MES7, [8] AS MES8, [9] AS MES9, [10] AS MES10,
               [11] AS MES11, [12] AS MES12
FROM (SELECT CODVEN, VLR_TOTAL, MONTH(DATA_EMISSAO) AS MES
      FROM PEDIDOS
      WHERE YEAR(DATA_EMISSAO) = 2006) P
   PIVOT( SUM(VLR_TOTAL) FOR MES IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS PVT
ORDER BY 1 ;       




SELECT CODVEN, SUM(VLR_TOTAL), MONTH(DATA_EMISSAO) AS MES
      FROM PEDIDOS
      WHERE YEAR(DATA_EMISSAO) = 2006
      GROUP BY CODVEN, MONTH(DATA_EMISSAO) WITH ROLLUP 




SET SHOWPLAN_ALL OFF;
   


--
SELECT CODCLI, [1] AS MES1, [2] AS MES2, [3] AS MES3, [4] AS MES4, [5] AS MES5, 
               [6] AS MES6, [7] AS MES7, [8] AS MES8, [9] AS MES9, [10] AS MES10,
               [11] AS MES11, [12] AS MES12
FROM (SELECT CODCLI, VLR_TOTAL, MONTH(DATA_EMISSAO) AS MES
      FROM PEDIDOS
      WHERE YEAR(DATA_EMISSAO) = 2006) P
   PIVOT( SUM(VLR_TOTAL) FOR MES IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS PVT
ORDER BY 1       


 
---
SELECT ID_PRODUTO, [1] AS MES1, [2] AS MES2, [3] AS MES3, [4] AS MES4, [5] AS MES5, 
               [6] AS MES6, [7] AS MES7, [8] AS MES8, [9] AS MES9, [10] AS MES10,
               [11] AS MES11, [12] AS MES12
FROM (SELECT I.ID_PRODUTO, I.QUANTIDADE*I.PR_UNITARIO AS VALOR, MONTH(P.DATA_EMISSAO) AS MES
      FROM PEDIDOS P JOIN ITENSPEDIDO I ON P.NUM_PEDIDO = I.NUM_PEDIDO
      WHERE YEAR(P.DATA_EMISSAO) = 2006) I
   PIVOT( SUM(VALOR) FOR MES IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS PVT
ORDER BY 1     




------------------------
SELECT ANO, [1] AS MES1, [2] AS MES2, [3] AS MES3, [4] AS MES4, [5] AS MES5, 
               [6] AS MES6, [7] AS MES7, [8] AS MES8, [9] AS MES9, [10] AS MES10,
               [11] AS MES11, [12] AS MES12
FROM (SELECT YEAR(DATA_EMISSAO) AS ANO, VLR_TOTAL, MONTH(DATA_EMISSAO) AS MES
      FROM PEDIDOS) P
   PIVOT( SUM(VLR_TOTAL) FOR MES IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS PVT
ORDER BY 1 




------------------------

SELECT MES, [2005] AS ANO_2005, [2006] AS ANO_2006, [2007] AS ANO_2007, [2008] AS ANO_2008
FROM ( SELECT MONTH(DATA_EMISSAO) AS MES,  VLR_TOTAL, YEAR(DATA_EMISSAO) AS ANO FROM PEDIDOS ) P
PIVOT( SUM(VLR_TOTAL) FOR ANO IN ([2005],[2006],[2007],[2008]) )     AS PVT
ORDER BY 1






CREATE TABLE FREQ_CINEMA
( DIA_SEMANA TINYINT, 
  SEC_14HS   INT, 
  SEC_16HS   INT, 
  SEC_18HS   INT, 
  SEC_20HS   INT, 
  SEC_22HS   INT )
  
INSERT FREQ_CINEMA VALUES ( 1, 80, 100, 130, 90, 70 )  
INSERT FREQ_CINEMA VALUES ( 2, 20, 34, 75, 50, 30 )
INSERT FREQ_CINEMA VALUES ( 3, 25, 40, 80, 70, 25 )
INSERT FREQ_CINEMA VALUES ( 4, 30, 45, 70, 50, 30 )
INSERT FREQ_CINEMA VALUES ( 5, 35, 40, 60, 60, 40 )
INSERT FREQ_CINEMA VALUES ( 6, 25, 34, 70, 90, 110 )
INSERT FREQ_CINEMA VALUES ( 7, 30, 80, 130, 150, 180 )


SELECT * FROM FREQ_CINEMA
               
SELECT DIA_SEMANA, HORARIO, QTD_PESSOAS               
FROM
(               
SELECT DIA_SEMANA, SEC_14HS, SEC_16HS, SEC_18HS, SEC_20HS, SEC_22HS
FROM FREQ_CINEMA
) P
UNPIVOT ( QTD_PESSOAS FOR HORARIO IN (SEC_14HS, SEC_16HS, SEC_18HS, SEC_20HS, SEC_22HS)) AS UP
